drop sequence board_spring_seq;
select * from wmt_teacher;
select * from WMT_STUDENT;
drop table wmt_teacher;

 create table wmt_teacher(
 	teacherid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null,
	gender varchar2(50) not null,
	tel varchar2(50) not null,
	hoperegion varchar2(50) not null,
	email varchar2(50) not null,
	questionpass varchar2(100) not null,
	answerpass varchar2(100) not null,
	originalfilename varchar2(100),
	savefilename varchar2(100),
	enrolldate varchar2(50) not null,
	withdrawaldate varchar2(50) not null,
	university varchar2(50) not null,
	hopesubject varchar2(50) not null,
	hopegender varchar2(50) not null,
	availablestudent varchar2(50) not null,
	availableday varchar2(50) not null,
	availabletime varchar2(50) not null,
	intro varchar2(1000) not null
 )
  create table wmt_student(
   studentid varchar2(50) primary key,
   name varchar2(50) not null,
   password varchar2(50) not null,
   gender varchar2(50) not null,
   tel varchar2(50) not null,
   hoperegion varchar2(50) not null,
   email varchar2(50) not null,
   questionpass varchar2(100) not null,
   answerpass varchar2(100) not null,
   enrolldate varchar2(50) not null,
   withdrawaldate varchar2(50) not null,
   hopesubject varchar2(150) not null,
   zipcode varchar2(10) not null,
   codeaddress varchar2(200) not null,
   subaddress varchar2(150) not null
   
  )

  insert into WMT_teacher values('java','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  
 insert into WMT_student values('java2','수지','1111','여','1234','분당','suzi@naver.com','할아버지 성함?','수지할배',
  '0619','0620','수학','20','성남시 분당구', '703동 502호');
-- 현 시퀀스의 다음 값을 조회 
select board_spring_seq.nextval from dual;

insert into board_spring(no,title,writer,password,content,time_posted) 
values(board_spring_seq.nextval,'테스트','아이유','a','ㅋㅋㅋ',sysdate);
select * from board_spring;

delete from BOARD_SPRING where no='1';
-- 글 조회

----------------------------------------------------------------------------------
-- 추가사항에 맟춰서 테이블 변경
alter table board_spring 
add(ref number default 0 not null,restep number default 0 not null,relevel number default 0 not null);
update BOARD_SPRING set ref=no;

-- 테이블 조회
select * from BOARD_SPRING;

--글 쓸 때 insert 문
insert into BOARD_SPRING(no,title,writer,password,content,time_posted,ref,restep,relevel) 
values(board_spring_seq.nextval,'test1','test1','test1','tttttest1',sysdate,board_spring_seq.nextval,0,0)

--댓글 쓸 때 insert 문
--insert into BOARD_SPRING(no,title,writer,password,content,time_posted,ref,restep,relevel) 
--values(board_spring_seq.nextval,'reply','test2','test2','tttttest2',sysdate,부모ref,부모step+1,부모level+1);
insert into BOARD_SPRING(no,title,writer,password,content,time_posted,ref,restep,relevel) 
values(board_spring_seq.nextval,'reply','test2','test2','tttttest2',sysdate,50,0+1,0+1);

--댓글쓸 때 형제 restep 하나씩 증가시키기
--update BOARD_SPRING 
--set restep=restep+1 
--where 내스텝<=restep and no!=내no and ref=(내ref or 부모ref);
update BOARD_SPRING 
set restep=restep+1 
where 1<=restep and no!=54 and ref=50;

-- 글 목록 보여주기
select no,title, writer,
time_posted, hits, ref, restep, relevel from (
select no,title, writer, time_posted, hits, ref, restep, relevel,
ceil(rownum/5) as page from (
select no,title, writer,
to_char(time_posted,'YYYY.MM.DD') as
time_posted,
hits, ref, restep, relevel from board_spring
order by ref desc, restep asc
)
)where page=1









